﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Diagnostics;
using System.Data;

namespace plust.Persistencia
{
    class SqlLiteHandler
    {
        private static String StringConexion = "Data Source=|DataDirectory|/Database/plust.db;Version=3;Compress=True;";
        private SQLiteConnection _Conexion;
        private SQLiteCommand _Query;
        private SQLiteDataAdapter _Adaptador;

        public SqlLiteHandler()
        {
            Debug.WriteLine("Constructor - SQL Lite");
            _Conexion = new SQLiteConnection(StringConexion);
        }

        private void conectar()
        {
            Debug.WriteLine("Conectando ... ");
            try
            {
                if (this._Conexion == null)
                {
                    this._Conexion = new SQLiteConnection(StringConexion);
                }
                this._Conexion.Open();
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine("ERROR Conectando a la Base de Datos : " + ex.Message);
            }
        }

        private void desconectar()
        {
            Debug.WriteLine("Desconectando...");
            if (this._Conexion != null)
            {
                this._Conexion.Close();
            }
        }

        public DataTable ejecutarConRetorno(string sql)
        {
            DataTable data = new DataTable();
            DataSet datos = new DataSet("RETORNO");
            try
            {

                this.conectar();
                Debug.WriteLine(sql);
                this._Query = new SQLiteCommand(sql, this._Conexion);
                this._Adaptador = new SQLiteDataAdapter(_Query);

                this._Adaptador.Fill(datos);

                return datos.Tables[0];

            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine("ERROR Ejecutando Con Retorno : " + ex.Message);
                throw ex;
            }
            finally
            {
                this.desconectar();
            }

        }

        public void ejecutarSinRetorno(string sql)
        {
            int rows = 0;

            try
            {

                this.conectar();
                Debug.WriteLine(sql);
                this._Query = new SQLiteCommand(sql, this._Conexion);
                this._Query.ExecuteNonQuery();

            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine("ERROR Ejecutando Sin Retorno : " + ex.Message);
            }
            finally
            {
                this.desconectar();
            }
        }
    }
}
